package com.open.exam.sqlprovider;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.util.HashMap;
import java.util.Map;

/**
 * @author 奶茶
 * Created on 2017/11/15.
 */
public class ExamMoniterProvider {

    private static Logger logger = LoggerFactory.getLogger(ExamMoniterProvider.class);

    public String findUnifiedExamList(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String examSiteId = (String) parameter.get("examSiteId");
        Short planType = Short.valueOf(parameter.get("planType").toString());
        Integer unifiedExam = (Integer) parameter.get("unifiedExam");
        Integer examPlanSysState = (Integer) parameter.get("examPlanSysState");

        stringBuffer.append("SELECT data1.AppID,data1.ExamPlanName AS ExamPlanInfoName,data1.SeatByNumberRequired,data1.ExamSceneStartDelayTime,data1.ExamMode,data2.ExamRoomID,data1.ExamPlanInfoID,data4.ExamSceneName,data4.PlanBeginTime AS ExamSceneBeginTime,data5.ExamRoomName,data1.EndTime,data1.ExamSceneEndDelayTime,data2.ExamSceneID,data4.PlanBeginTime FROM ");
        stringBuffer.append("( SELECT * FROM ExamPlanInfo WHERE ExamSiteID = \'").append(examSiteId).append("\' AND ExamPlanType = ").append(planType).append(" ) AS data1 JOIN ");
        stringBuffer.append("( SELECT arrange.AppID,arrange.ExamPlanInfoID,arrange.ExamRoomID,arrange.ExamSceneID FROM ExamineeArrange AS arrange JOIN ( SELECT * FROM ExamPlanInfo WHERE ExamSiteID = \'").append(examSiteId).append("\' AND ExamPlanType = ").append(planType).append(" ) AS plan ON arrange.ExamPlanInfoID = plan.ExamPlanInfoID GROUP BY arrange.AppID,arrange.ExamPlanInfoID,arrange.ExamRoomID,arrange.ExamSceneID ) AS data2 ON data1.ExamPlanInfoID = data2.ExamPlanInfoID ");
        stringBuffer.append("JOIN ExamScenes AS data4 ON data2.ExamSceneID = data4.ExamSceneID ");
        stringBuffer.append("JOIN ExamRoom AS data5 ON data2.ExamRoomID = data5.ExamRoomID ");
        stringBuffer.append("WHERE data1.AppID = data2.AppID AND data2.AppID = data4.AppID AND data2.ExamPlanInfoID = data4.ExamPlanInfoID AND data1.ExamMode = ").append(unifiedExam).append(" AND data1.SysState = ").append(examPlanSysState);

        String sql = stringBuffer.toString();
        logger.info("sql:{}",sql);
        return sql;
    }

    public String findFreeExamList(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String examSiteId = (String) parameter.get("examSiteId");
        Short planType = Short.valueOf(parameter.get("planType").toString());
        Integer freeExam = (Integer) parameter.get("freeExam");
        Integer examPlanSysState = (Integer) parameter.get("examPlanSysState");

        stringBuffer.append("SELECT data1.AppID,data1.ExamPlanName AS ExamPlanInfoName,data1.SeatByNumberRequired,data1.ExamSceneStartDelayTime,data1.ExamMode,data2.ExamRoomID,data1.ExamPlanInfoID,data4.ExamSceneName,data4.PlanBeginTime AS ExamSceneBeginTime,data5.ExamRoomName,data1.EndTime,data1.ExamSceneEndDelayTime,data2.ExamSceneID,data4.PlanBeginTime FROM ");
        stringBuffer.append("( SELECT * FROM ExamPlanInfo WHERE ExamSiteID = \'").append(examSiteId).append("\' AND ExamPlanType = ").append(planType).append(" ) AS data1 JOIN ");
        stringBuffer.append("( SELECT arrange.AppID,arrange.ExamPlanInfoID,arrange.ExamRoomID,arrange.ExamSceneID FROM ExamineeArrange AS arrange JOIN ( SELECT * FROM ExamPlanInfo WHERE ExamSiteID = \'").append(examSiteId).append("\' AND ExamPlanType = ").append(planType).append(" ) AS plan ON arrange.ExamPlanInfoID = plan.ExamPlanInfoID GROUP BY arrange.AppID,arrange.ExamPlanInfoID,arrange.ExamRoomID,arrange.ExamSceneID ) AS data2 ON data1.ExamPlanInfoID = data2.ExamPlanInfoID ");
        stringBuffer.append("JOIN ExamScenes AS data4 ON data2.ExamSceneID = data4.ExamSceneID ");
        stringBuffer.append("JOIN ExamRoom AS data5 ON data2.ExamRoomID = data5.ExamRoomID ");
        stringBuffer.append("WHERE data2.AppID = data4.AppID AND data2.ExamPlanInfoID = data4.ExamPlanInfoID AND data1.ExamMode = ").append(freeExam).append(" AND data1.SysState = ").append(examPlanSysState);

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String findExamingMonitorsByStats(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        stringBuffer.append("SELECT * FROM ExamingMonitor ");
        if(parameter.get("sysState") != null){
            stringBuffer.append("WHERE SysState = ").append((Integer)parameter.get("sysState"));
        }

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    //public String findExamCaseList(Map<String,Object> parameter) {
    //    StringBuffer sb = new StringBuffer();
    //    sb.append("SELECT\n" +
    //            "\tdataMonitor.IfDeregulation ifDeregulation,\n" +
    //            "\tIFNULL(\n" +
    //            "\t\tdataExamPhoto.FaceCompareResult,\n" +
    //            "\t\t''\n" +
    //            "\t) faceCompareResult,\n" +
    //            "\tdataArrange.ExamSceneID examSceneID,\n" +
    //            "\tdataArrange.ExamineeArrangeID examineeArrangeID,\n" +
    //            "\tdataPlan.ExamPlanName examPlanName,\n" +
    //            "\tdataExaminee.ExamCardNo examCardNo,\n" +
    //            "\tdataSubject.SubjectName examSubjectName,\n" +
    //            "\tdataSubject.ExamSubjectID examSubjectID,\n" +
    //            "\tdataArrange.PlanEndTime endTime,\n" +
    //            "\tdataMonitor.SysState examStatus,\n" +
    //            "\tdataArrange.SeatNo seatNo,\n" +
    //            "\tdataMonitor.DelayMinute delayedTime,\n" +
    //            "\tdataArrange.ExamineeName examineeName,\n" +
    //            "\tdataArrange.ExamineeID examineeID,\n" +
    //            "\tdataMonitor.RetainTime retainTime,\n" +
    //            "\tdataMonitor.LoginTime loginTime,\n" +
    //            "\tdataMonitor.ExamStartTime relBeginTime,\n" +
    //            "\t(\n" +
    //            "\t\tSELECT\n" +
    //            "\t\t\tCOUNT(1)\n" +
    //            "\t\tFROM\n" +
    //            "\t\t\tExamPhoto photo\n" +
    //            "\t\tWHERE\n" +
    //            "\t\t\tdataArrange.ExamineeArrangeID = photo.ExamineeArrangeID\n" +
    //            "\t\tAND photo.PhotoResourceType = 1\n" +
    //            "\t\tAND photo.FaceCompareResult = 0\n" +
    //            "\t) faceCompareFailCount\n" +
    //            "FROM ");
    //
    //    sb.append("ExamineeArrange AS dataArrange\n" +
    //            "JOIN ExamPlanInfo AS dataPlan ON dataArrange.ExamPlanInfoID = dataPlan.ExamPlanInfoID\n" +
    //            "JOIN Examinee AS dataExaminee ON dataArrange.ExamineeID = dataExaminee.ExamineeID\n" +
    //            "JOIN ExamSubject AS dataSubject ON dataArrange.ExamSubjectID = dataSubject.ExamSubjectID\n" +
    //            "JOIN ExamingMonitor AS dataMonitor ON dataArrange.ExamPlanInfoID = dataMonitor.ExamPlanInfoID\n" +
    //            "LEFT JOIN ExamPhoto dataExamPhoto ON dataArrange.ExamineeArrangeID = dataExamPhoto.ExamineeArrangeID\n");
    //
    //    sb.append("WHERE dataArrange.AppID = dataPlan.AppID AND dataExaminee.AppID = dataArrange.AppID ");
    //    sb.append("AND dataExaminee.ExamSiteID = \'").append(parameter.get("siteId")).append("\' ");
    //    sb.append("AND dataExaminee.ExamPlanInfoID = dataArrange.ExamPlanInfoID\n" +
    //            "AND dataSubject.AppID = dataArrange.AppID\n" +
    //            "AND dataSubject.ExamPlanInfoID = dataArrange.ExamPlanInfoID\n" +
    //            "AND dataArrange.ExamineeID = dataMonitor.ExamineeID\n" +
    //            "AND dataArrange.ExamSubjectID = dataMonitor.ExamSubjectID\n" +
    //            "AND dataArrange.AppID = dataMonitor.AppID ");
    //    sb.append("AND dataArrange.ExamPlanInfoID = \'").append(parameter.get("planId")).append("\' ");
    //    sb.append("AND dataArrange.ExamRoomID = \'").append(parameter.get("roomId")).append("\' ");
    //    sb.append("AND dataArrange.AppID = \'").append(parameter.get("appId")).append("\' ");
    //    if(parameter.get("sceneId") != null){
    //        sb.append("AND dataArrange.ExamSceneID = \'").append((String)parameter.get("sceneId")).append("\' ");
    //    }
    //    if(parameter.get("subjectId") != null){
    //        sb.append("AND ExamSubjectID = \'").append((String)parameter.get("subjectId")).append("\' ");
    //    }
    //    if(parameter.get("examCardNo") != null && !("").equals(parameter.get("examCardNo"))){
    //        sb.append("AND dataExaminee.ExamCardNo = \'").append((String)parameter.get("examCardNo")).append("\' ");
    //    }
    //    if(parameter.get("examineeName") != null){
    //        sb.append("AND dataExaminee.ExamineeName LIKE \'%").append(parameter.get("examineeName")).append("%\' ");
    //    }
    //    if(parameter.get("sysState") != null){
    //        sb.append("AND dataMonitor.SysState = ").append(parameter.get("sysState"));
    //    }
    //
    //    String sql = sb.toString();
    //    logger.info(sql);
    //    return sql;
    //
    //}

    public String findExamCaseList(Map<String,Object> parameter){
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT\n" +
                "\tdatamoniter.IfDeregulation ifDeregulation,\n" +
                "\tIFNULL(\n" +
                "\t\tdataphoto.FaceCompareResult,\n" +
                "\t\t''\n" +
                "\t) faceCompareResult,\n" +
                "\tdataarrange.ExamSceneID examSceneID,\n" +
                "\tdataarrange.ExamineeArrangeID examineeArrangeID,\n" +
                "\tdataplan.ExamPlanName examPlanName,\n" +
                "\tdataarrange.ExamineeID examCardNo,\n" +
                "\tdatasubject.SubjectName examSubjectName,\n" +
                "\tdatasubject.ExamSubjectID examSubjectID,\n" +
                "\tdataarrange.PlanEndTime endTime,\n" +
                "\tdatamoniter.SysState examStatus,\n" +
                "\tdataarrange.SeatNo seatNo,\n" +
                "\tdatamoniter.DelayMinute delayedTime,\n" +
                "\tdataarrange.ExamineeName examineeName,\n" +
                "\tdataarrange.ExamineeID examineeID,\n" +
                "\tdatamoniter.RetainTime retainTime,\n" +
                "\tdatamoniter.LoginTime loginTime,\n" +
                "\tdatamoniter.ExamStartTime relBeginTime\n" +
                "FROM ");
        sb.append("examineearrange dataarrange\n" +
                "LEFT JOIN examingmonitor datamoniter ON datamoniter.ExamPlanInfoID = dataarrange.ExamPlanInfoID\n" +
                "AND datamoniter.ExamSubjectID = dataarrange.ExamSubjectID\n" +
                "AND datamoniter.ExamineeID = dataarrange.ExamineeID\n" +
                "LEFT JOIN examplaninfo dataplan ON dataplan.ExamPlanInfoID = datamoniter.ExamPlanInfoID\n" +
                "LEFT JOIN examsubject datasubject ON datasubject.ExamPlanInfoID = dataplan.ExamPlanInfoID\n" +
                "AND datasubject.ExamSubjectID = datamoniter.ExamSubjectID\n" +
                "LEFT JOIN examphoto dataphoto ON dataphoto.ExamineeArrangeID = dataarrange.ExamineeArrangeID AND dataphoto.PhotoResourceType = 2 \n");

        sb.append("WHERE dataplan.AppID = \'").append(parameter.get("appId")).append("\' ");
        sb.append("AND dataarrange.ExamPlanInfoID = \'").append(parameter.get("planId")).append("\' ");
        sb.append("AND dataarrange.ExamSceneID = \'").append(parameter.get("sceneId")).append("\' ");
        sb.append("AND dataarrange.ExamRoomID = \'").append(parameter.get("roomId")).append("\' ");

        if(parameter.get("subjectId") != null){
            sb.append("AND dataarrange.ExamSubjectID = \'").append(parameter.get("subjectId")).append("\' ");
        }
        if(parameter.get("examCardNo") != null && !("").equals(parameter.get("examCardNo"))){
            sb.append("AND dataarrange.ExamineeID = \'").append(parameter.get("examCardNo")).append("\' ");
        }
        if(parameter.get("examineeName") != null){
            sb.append("AND dataarrange.ExamineeName LIKE \'%").append(parameter.get("examineeName")).append("%\' ");
        }
        if(parameter.get("sysState") != null){
            sb.append("AND datamoniter.SysState = ").append(parameter.get("sysState"));
        }

        sb.append(" GROUP BY dataarrange.ExamPlanInfoID,dataarrange.ExamSubjectID,dataarrange.ExamineeID ");



        String sql = sb.toString();
        logger.info(sql);
        return sql;

    }

    public String findExamingMonitorsBySomeWhere(Map<String,Object> parameter){
        StringBuilder sb = new StringBuilder();
        String appID = (String) parameter.get("appID");
        String examPlanInfoID = (String) parameter.get("examPlanInfoID");
        String examRoomID = (String) parameter.get("examRoomID");
        String examSceneID = (String) parameter.get("examSceneID");

        sb.append("SELECT * FROM ExamingMonitor WHERE AppID = \'").append(appID).append("\' AND ExamPlanInfoID = \'").append(examPlanInfoID).append("\' AND ExamRoomID = \'").append(examRoomID).append("\' AND ExamSceneID = \'").append(examSceneID).append("\'");

        String sql = sb.toString();
        logger.info(sql);
        return sql;
    }

    public String getExamStudentInfoViewModelBySomeWhere(Map<String,Object> parameter){
        StringBuilder sb = new StringBuilder();
        String examineeId = (String) parameter.get("examineeId");
        String appId = (String) parameter.get("appId");
        String planId = (String) parameter.get("planId");

        sb.append("SELECT data1.AccountType,data1.CertificateNo,data1.CertificateType,data1.ExamCardNo,data1.ExamineeName,dataExts.Photo,dataExts.PhotoResource,data1.Sex FROM ( ");
        sb.append("SELECT * FROM Examinee WHERE ExamineeID = \'").append(examineeId).append("\' AND AppID = \'").append(appId).append("\' AND ExamPlanInfoID = \'").append(planId).append("\' ) AS data1 ");
        sb.append("LEFT JOIN ExamineeExt AS dataExts ON data1.ExamineeID = dataExts.ExamineeID AND data1.AppID = dataExts.AppID LIMIT 1");


        String sql = sb.toString();
        logger.info(sql);
        return sql;
    }


    /**
     * 获取考生签到列表
     * @param parameter
     * @return
     */
    public String getSignInList(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String planId = (String) parameter.get("planId");
        String roomId = (String) parameter.get("roomId");
        String sceneId = (String) parameter.get("sceneId");
        String examineeName = (String) parameter.get("examineeName");
        String examCardNo = (String) parameter.get("examCardNo");
        Integer statusId = (Integer)parameter.get("statusId");
        String examSiteId = (String)parameter.get("examSiteId");

        stringBuffer.append("SELECT \n" +
                "DISTINCT e.ExamCardNo,\n" +
                "em.IDCardResult,\n" +
                "em.IDCardTime,\n" +
                "em.IDCardReason,\n" +
                "em.AppID,\n" +
                "em.ExamineeID,\n" +
                "em.ExamPlanInfoID,\n" +
                "em.ExamSceneID,\n" +
                "em.ExamRoomID,\n" +
                "e.ExamineeName,\n" +
                "e.Sex,\n" +
                "e.CertificateNo,\n" +
                "FALSE as todayIDCard " +
                "from ExamingMonitor em \n" +
                "LEFT JOIN Examinee e on e.ExamineeID = em.ExamineeID\n" +
                "WHERE  e.AppID = em.AppID \n" +
                "AND e.ExamPlanInfoID = em.ExamPlanInfoID "
               ).append(" AND e.ExamSiteID = \'").append(examSiteId).append("\' ")
                .append(" AND em.AppID = \'").append(appId).append("\'")
                .append(" AND em.ExamPlanInfoID = \'").append(planId).append("\' ")
               ;

        if(sceneId != null){
            stringBuffer.append(" AND em.ExamSceneID = \'").append(sceneId).append("\' ");
        }

        if(roomId != null){
            stringBuffer.append(" AND em.ExamRoomID = \'").append(roomId).append("\' ");
        }
        if(!StringUtils.isEmpty(examineeName)){
            stringBuffer.append(" AND e.ExamineeName like \'%").append(examineeName).append("%\'");
        }
        if(!StringUtils.isEmpty(examCardNo)){
            stringBuffer.append(" AND e.ExamCardNo like \'%").append(examCardNo).append("%\'");
        }

        //刷卡状态：null 查询所有; 0 未刷卡；1 系统确认；2 人工确认
        if(statusId != null){
            //都为空则为随考   签到状态需要加当天开始和结束时间为判断条件
            if(sceneId == null && roomId == null){
                //随考未签到 两个判断
                //1，IDCardResult = 0
                //2，IDCardResult ！= 0  并且签到时间不是今天
                if(statusId != null && statusId == 0){
                    stringBuffer.append(" AND (em.IDCardResult = ").append(statusId);
                    stringBuffer.append(" OR em.IDCardResult != ").append(statusId)
                            .append(" AND  IDCardTime NOT BETWEEN \n" +
                                    " str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s') \n" +
                                    " AND \n" +
                                    " DATE_ADD(DATE_ADD(str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'),INTERVAL 1 DAY),INTERVAL -1 SECOND))");
                }else {
                    if (statusId != null && statusId == 1) {
                        stringBuffer.append(" AND em.IDCardResult = ").append(statusId);
                    } else if (statusId != null && statusId == 2) {
                        stringBuffer.append(" AND em.IDCardResult = ").append(statusId);
                    }
                    stringBuffer.append(" AND em.IDCardTime BETWEEN \n" +
                            "str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s') \n" +
                            "and \n" +
                            "DATE_ADD(DATE_ADD(str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'),INTERVAL 1 DAY),INTERVAL -1 SECOND) ");
                }
                }else{
                if(statusId != null && statusId == 0){
                    stringBuffer.append(" AND em.IDCardResult = ").append(statusId);
                }else if(statusId != null && statusId == 1){
                    stringBuffer.append(" AND em.IDCardResult = ").append(statusId);
                }else if(statusId != null && statusId == 2){
                    stringBuffer.append(" AND em.IDCardResult = ").append(statusId);
                }
            }
        }

        String sql = stringBuffer.toString();
        return sql;
    }

    public static void main(String[] args){
//        Map<String,Object> map = new HashMap<>();
//        map.put("appId","1");
//        map.put("siteId",46);
//        map.put("planId",66);
//        map.put("roomId",21);
//        map.put("sysState",0);
//        map.put("subjectId",246);
//        map.put("sceneId",154);
//        map.put("examCardNo","CS731001");
//        map.put("examineeName","阳");
//        findExamCaseList(map);

        Map<String,Object> map = new HashMap<>();
        map.put("appId","1");
        map.put("examineeId",49508);
        map.put("planId",66);
        map.put("examSceneID",154);
//        getExamStudentInfoViewModelBySomeWhere(map);
    }
}
